package JdbcTemplate;

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import utils.JDBCUtils;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class Demo2 {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /*有关update的操作*/
    @Test
    //修改一条记录
    public void test1()
    {

        String sql = "update emp set salary = 10000 where id = 1001";
        //执行sql
        int count = template.update(sql);
        System.out.println(count);
    }

    @Test
    //添加一条记录
    public void test2()
    {
         String sql = "insert into emp (id,ename,dept_id) values (?,?,?)";
        System.out.println(template.update(sql,1015,"沙悟净",10));
    }

    @Test
    //删除一条记录
    public void test3()
    {
        String sql = "delete from emp where id = ?";
        System.out.println(template.update(sql,1015));
    }

    /*数据查询方法*/
    @Test
    // 查询id为1001的记录,并记录在MAP表格里
    public void test4()
    {
        String sql = "select * from emp where id = ?";
        Map<String,Object> map = template.queryForMap(sql,1001);
        //这里是将id作为key,其他的内容作为一个类封装起来
       // {id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
        //这里只能封装一条记录
        System.out.println(map);
    }
    @Test
    // 查询ALL记录
    public void test5()
    {
        String sql = "select * from emp";
        List<Map<String,Object>> list = template.queryForList(sql);
        //这里是将id作为key,其他的内容作为一个类封装起来
        // {id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}
        //这里只能封装一条记录
        for (Map<String, Object> objectMap : list) {
            System.out.println(objectMap);
        }
    }

    @Test
    // 查询ALL记录,并封装为emp对象
    public void test6()
    {
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept = rs.getInt("dept_id");

                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept(dept);
                return emp;
            }
        });

        for (Emp emp : list) {
            System.out.println(emp);
        }
    }
    @Test
    // 查询ALL记录,并封装为emp对象
    public void test6_2() {
        String sql = "select * from emp";
        List<Emp> list = template.query(sql,new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }


    @Test
    //查询总记录数
    public void test7() {
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql,Long.class);
        System.out.println(total);
        //14
    }
}
